﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
   public class UserCollection
    {
        public UserCollection()
        {
            
        }
        public bool Add(User user)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_USERS (UserName,Password,Role) Values
                   ('" + user.UserName + "','"
                   + user.Password + "',"
                   + user.Role + ")";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(User user)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_USERS Set 
                     UserName='" + user.UserName +  
                     "',Password='" + user.Password + 
                     "',Role=" + user.Role + " Where UserID=" + user.UserID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(User user)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_USERS Where ID= " + user.UserID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(User user)
        {
            try
            {
                string wherestatement = WhereStatement(user);
                string command = @"SELECT        UserID, UserName, Password, Role
FROM            SOCCER_USERS";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID) AS MaxID FROM   SOCCER_USERS";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public string WhereStatement(User user)
        {
            string wherestatement = "";
            if (user.UserID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_USERS.UserID= " + user.UserID;
                }
                else
                {
                    wherestatement += "SOCCER_USERS.UserID= " + user.UserID;
                }
            }
            if (user.UserName != null)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_USERS.UserName = '" + user.UserName + "' ";
                }
                else
                {
                    wherestatement += " SOCCER_USERS.UserName = '" + user.UserName + "' ";
                }
            }
            if (user.Password != null)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_USERS.Password = '" + user.Password + "' ";
                }
                else
                {
                    wherestatement += " SOCCER_USERS.Password = '" + user.Password + "' ";
                }
            }
            return wherestatement;
        }
    }
}
